package chapter3.test01;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.dao.EmptyResultDataAccessException;

import vo.UserVO;

public class UserDao {
	
	private DataSource dataSource ;
	

	public void setDataSource(DataSource dataSource){
		this.dataSource = dataSource;
	}
	
	public void add(UserVO user) throws SQLException{
		
		Connection  c =  dataSource.getConnection();
		PreparedStatement psmt = c.prepareStatement("insert into users(id,name,password) values (?,?,?)");
		psmt.setString(1, user.getId());
		psmt.setString(2,user.getName());
		psmt.setString(3,user.getPassword());
		
		psmt.executeUpdate();
		
		psmt.close();
		c.close();
		
	}
		
	public UserVO get(String id) throws SQLException{
		
		Connection c = null;
		PreparedStatement psmt = null;
		ResultSet rs = null;
		UserVO user = null;
		
		try{
			 c =  dataSource.getConnection();	
			
			 psmt = c.prepareStatement("select * from users where id = ?");
			
			psmt.setString(1, id);
			
			 rs =  psmt.executeQuery();
			
			while(rs.next()){
				user = new UserVO();
				user.setId(rs.getString("id"));
				user.setName(rs.getString("name"));
				user.setPassword(rs.getString("password"));
			}
		}catch(SQLException e){
			throw e;
		}finally{
			if(rs != null){
				try{
						rs.close();
				}catch(SQLException e){
					
				}
			}
			if(psmt != null){
				try{
					psmt.close();
				}catch(SQLException e){
					
				}
			}
			if(c != null){
				try{
						c.close();
				}catch(SQLException e){
					
				}
			}	
		}
		
		
		if(user == null) throw new EmptyResultDataAccessException(1);
		
		return user;
		
	}
	
	public void deleteAll() throws SQLException{
		
		Connection c =null;
		PreparedStatement psmt = null;
		
		try{
				c =  dataSource.getConnection();
				psmt = c.prepareStatement("delete from users");
				psmt.executeUpdate();
		}catch(SQLException e){
			throw e;
		}finally{
			 if(psmt != null){
				 try{
					 psmt.close();
				 } catch (SQLException e){
					 
				 }
			 }
			 if(c!= null){
				 try{
					 psmt.close();
				 }catch(SQLException e){
					 
				 }
			 }
		}
		
		psmt.close();
		c.close();
		
	}
	
	public int getCount() throws SQLException {
		Connection c = dataSource.getConnection();
		
		PreparedStatement ps = c.prepareStatement("select  count(*) from users");
		
		ResultSet rs = ps.executeQuery();
		rs.next();
		int count  = rs.getInt(1);
		return count;
	}
	
	
}
